package cn.dida.dao;

import cn.dida.model.LgsPdaUser;
import cn.dida.model.Pageable;
import cn.dida.util.MD5Util;
import org.apache.commons.lang3.ArrayUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by zhangzhiling on 2017/2/8.
 */
@Repository
public class LgsPdaUserDao extends DaoBase {

    public Pageable<LgsPdaUser> query(Integer companyId, String keyword,String realName,String depotName, Integer sta, Integer page) {

        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from lgs_pda_user lgs join lgs_depot dep on dep.id = lgs.depot_id ");
        StringBuffer sqlQueryResult = new StringBuffer("select lgs.ID, lgs.depot_id, lgs.account, lgs.real_name, lgs.mobile, lgs.sta, lgs.c_time, lgs.c_admin, dep.name 'depotName' " +
                "from lgs_pda_user lgs join lgs_depot dep on dep.id = lgs.depot_id ");

        StringBuffer sqlWhere = new StringBuffer(" where lgs.company_id  = ?") ;
        args = ArrayUtils.add(args, companyId);
        if(keyword != null && keyword != ""){
            sqlWhere.append(" and lgs.account like ? ");
            args = ArrayUtils.add(args, "%" + keyword + "%");
        }
        if(realName != null && realName != ""){
            sqlWhere.append(" and lgs.real_name like ? ");
            args = ArrayUtils.add(args, "%" + realName + "%");
        }
        if(depotName != null && depotName != ""){
            sqlWhere.append(" and dep.name like ? ");
            args = ArrayUtils.add(args, "%" + depotName + "%");
        }
        if(sta != null){
            sqlWhere.append(" and lgs.sta = ? ");
            args = ArrayUtils.add(args, sta);
        }


        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by lgs.c_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<LgsPdaUser> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(LgsPdaUser.class));
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }

    public Integer insert(LgsPdaUser lgsPdaUser){

        String sql = "insert into lgs_pda_user(company_id,depot_id, account, pwd, real_name, mobile, sta, c_time, c_admin) " +
                "values (:companyId,:depotId, :account, :pwd, :realName, :mobile, :sta, :cTime, :cAdmin)";

//        HashMap<String, Object> paramMap = new HashMap<>();
//        paramMap.put("companyId", lgsPdaUser.getCompanyId());
//        paramMap.put("depotId", lgsPdaUser.getDepotId());
//        paramMap.put("account", lgsPdaUser.getAccount());
//        paramMap.put("pwd", MD5Util.getMD5String(lgsPdaUser.getPwd()));
//        paramMap.put("realName", lgsPdaUser.getRealName());
//        paramMap.put("mobile", lgsPdaUser.getMobile());
//        paramMap.put("sta", lgsPdaUser.getSta());
//        paramMap.put("cTime", new Date().getTime());
//        paramMap.put("cAdmin", lgsPdaUser.getcAdmin());
        lgsPdaUser.setcTime(new Date().getTime());
        lgsPdaUser.setPwd(MD5Util.getMD5String(lgsPdaUser.getPwd()));
        KeyHolder keyHolder=new GeneratedKeyHolder();
        SqlParameterSource ps=new BeanPropertySqlParameterSource(lgsPdaUser);
        namedParameterJdbcTemplate.update(sql,  ps, keyHolder);
        return keyHolder.getKey().intValue();
    }

    /**
     * 添加PDA用户与操作类型关联
     * @param userId
     * @param nodeId
     * @return
     */
    public Integer insertPuserNode(Integer userId, Integer nodeId){
        String sql = "insert into res_puser_node(pk_user, pk_node,c_time) values (:userId, :nodeId,:cTime)";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("userId",userId);
        paramMap.put("nodeId", nodeId);
        paramMap.put("cTime", new Date().getTime());
        return namedParameterJdbcTemplate.update(sql, paramMap);

    }

    /**
     * 删除PDA用户与操作类型关联
     * @param userId
     * @return
     */
    public Integer  deletPuserNode(Integer userId){
        String sql = "delete from res_puser_node where pk_user = ? ";
        return jdbcTemplate.update(sql, userId);
    }

    /**
     * 获取pda用户关联操作类型
     * @param userId
     * @return
     */
    public List<Map<String,Object>> getNodeByUser(Integer userId){
        String sql = "select pk_node 'nodeId' from res_puser_node where pk_user = ? ";
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sql, userId);
        return result;
    }

    /**
     * 修改pda用户
     * @param lgsPdaUser
     * @return
     */
    public Integer update(LgsPdaUser lgsPdaUser){
        String sql = "update lgs_pda_user set sta=:sta " ;
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("sta", lgsPdaUser.getSta());
        if(lgsPdaUser.getPwd() != null){
            sql =  sql + ",pwd=:pwd";
            paramMap.put("pwd", MD5Util.getMD5String(lgsPdaUser.getPwd()));
        }
        sql +=  " where ID=" + lgsPdaUser.getId();
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 根据id获取pda用户详情
     * @param id
     * @return
     */
    public LgsPdaUser getPdaUser(int id) {
        String sql = "select lgs.ID, lgs.depot_id, lgs.account, lgs.real_name, lgs.mobile, lgs.sta, lgs.c_time, lgs.c_admin, dep.name 'depotName' from lgs_pda_user lgs join lgs_depot dep on dep.id = lgs.depot_id where lgs.id = ?";
        List<LgsPdaUser> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<LgsPdaUser>(LgsPdaUser.class), id);
        if (null != list && list.size() == 1) {
            return list.get(0);
        }
        return null;
    }

    /**
     * 验证用户名（账户）是否已存在
     * @param account
     * @return
     */
    public LgsPdaUser testAccount( String account){

        String sql = "select ID, depot_id, account, real_name, mobile, sta, c_time, c_admin from lgs_pda_user where  account = ?" ;
        List<LgsPdaUser>  list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<LgsPdaUser>(LgsPdaUser.class), account);
        if (null != list && list.size() == 1) {
            return list.get(0);
        }
        return null;
    }

    /**
     * 导出数据
     * @param companyId
     * @param keyword
     * @param realName
     * @param depotName
     * @param sta
     * @return
     */
    public List<LgsPdaUser> queryForExport(Integer companyId, String keyword, String realName, String depotName, Integer sta){
        Object[] args = {};
        StringBuffer sqlQueryResult = new StringBuffer("select lgs.ID, lgs.depot_id, lgs.account, lgs.real_name, lgs.mobile, lgs.sta, lgs.c_time, lgs.c_admin, dep.name 'depotName' " +
                "from lgs_pda_user lgs join lgs_depot dep on dep.id = lgs.depot_id ");
        StringBuffer sqlWhere = new StringBuffer(" where lgs.company_id = ?") ;
        args = ArrayUtils.add(args, companyId);
        if(keyword != null && keyword != ""){
            sqlWhere.append(" and lgs.account like ? ");
            args = ArrayUtils.add(args, "%" + keyword + "%");
        }
        if(realName != null && realName != ""){
            sqlWhere.append(" and lgs.real_name like ? ");
            args = ArrayUtils.add(args, "%" + realName + "%");
        }
        if(depotName != null && depotName != ""){
            sqlWhere.append(" and dep.name like ? ");
            args = ArrayUtils.add(args, "%" + depotName + "%");
        }
        if(sta != null){
            sqlWhere.append(" and lgs.sta = ? ");
            args = ArrayUtils.add(args, sta);
        }
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by lgs.c_time desc");
        List<LgsPdaUser> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(LgsPdaUser.class));
        return result;
    }


}
